package org.hgvc.webapp.ets.spring.dao;

import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hgvc.webapp.ets.bo.Agent;
import org.hgvc.webapp.ets.bo.AgentCommission;
import org.hgvc.webapp.ets.bo.AgreementControl;
import org.hgvc.webapp.ets.bo.CommissionPaymentSchema;
import org.hgvc.webapp.ets.bo.CommissionSchema;
import org.hgvc.webapp.ets.bo.CommissionSchemaMembership;
import org.hgvc.webapp.ets.bo.CommissionSchemaPaymentRange;
import org.hgvc.webapp.ets.bo.Membership;
import org.hgvc.webapp.ets.constant.AgentConstant;
import org.hgvc.webapp.ets.constant.AgreementConstant;
import org.hgvc.webapp.ets.constant.CommonConstant;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.jocsb.base.AbstractHibernateDao;

import com.j6.framework.util.DateUtil;


public class AgentHbmDao extends AbstractHibernateDao {
	public Agent getAgentByUserId(String userId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select agent from Agent agent where agent.refUser.userId=:userId");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("userId", userId);

		return (Agent)query.uniqueResult();
	}

	public List<Agent> getAgentByCode(String code)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select agent from Agent agent where agent.code=:code and agent.isDeleted=:value");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("code", code);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Agent> )query.list();
	}

	public List<CommissionPaymentSchema> getCommissionPaymentSchemaWithType(String type)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionPaymentSchema sch where sch.type=:type and sch.isDeleted=:value");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("type", type);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<CommissionPaymentSchema>)query.list();
	}

	public List<CommissionPaymentSchema> getCommissionPaymentSchemaWithTypeCodeName(String type,String code,String name)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionPaymentSchema sch where sch.type=:type and sch.isDeleted=:value");
		strb.append(" and (sch.code=:code or sch.name=:name)");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("type", type);
		query.setParameter("code", code);
		query.setParameter("name", name);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<CommissionPaymentSchema>)query.list();
	}

	
	public List<CommissionPaymentSchema> getCommissionPaymentSchemaWithAgentId(String agentId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionPaymentSchema sch where sch.agent.agentId=:agentId and sch.isDeleted=:value");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("agentId", agentId);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<CommissionPaymentSchema>)query.list();
	}

	public List<CommissionPaymentSchema> getCommissionPaymentSchemaWithAgentIdCodeName(String agentId,String code,String name)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionPaymentSchema sch where sch.agent.agentId=:agentId and sch.isDeleted=:value");
		strb.append(" and (sch.code=:code or sch.name=:name)");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("agentId", agentId);
		query.setParameter("code", code);
		query.setParameter("name", name);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<CommissionPaymentSchema>)query.list();
	}

	public List<CommissionSchema> getCommissionSchemaWithType(String type)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionSchema sch where sch.type=:type and sch.isDeleted=:value");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("type", type);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<CommissionSchema>)query.list();
	}

	public List<CommissionSchema> getCommissionSchemaWithTypeCodeName(String type,String code,String name)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionSchema sch where sch.type=:type and sch.isDeleted=:value");
		strb.append(" and (sch.code=:code or sch.name=:name)");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("type", type);
		query.setParameter("code", code);
		query.setParameter("name", name);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<CommissionSchema>)query.list();
	}

	public List<CommissionSchema> getCommissionSchemaWithAgentId(String agentId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionSchema sch where sch.agent.agentId=:agentId and sch.isDeleted=:value");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("agentId", agentId);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<CommissionSchema>)query.list();
	}

	public List<CommissionSchema> getCommissionSchemaWithAgentIdCodeName(String agentId,String code,String name)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionSchema sch where sch.agent.agentId=:agentId and sch.isDeleted=:value");
		strb.append(" and (sch.code=:code or sch.name=:name)");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("agentId", agentId);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("code", code);
		query.setParameter("name", name);
		return (List<CommissionSchema>)query.list();
	}

	public List<CommissionSchemaPaymentRange> getCommissionSchemaPaymentRangeBySchId(String id)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionSchemaPaymentRange sch where sch.commissionSchema.commSchId=:id and sch.isDeleted=:value");
		strb.append(" order by sch.commSchPayRangeId asc ");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("id", id);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<CommissionSchemaPaymentRange>)query.list();
	}

	public List<CommissionSchemaMembership> getCommissionSchemaMembershipBySchId(String id)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionSchemaMembership sch where sch.commSch.commSchId=:id and sch.isDeleted=:value");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("id", id);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<CommissionSchemaMembership>)query.list();
	}

	public CommissionSchemaPaymentRange getCommissionSchemaPaymentRangeBySchId(String id,Double fee, int affectYear, int affectMonth )throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select sch from CommissionSchemaPaymentRange sch where sch.commissionSchema.commSchId=:id and sch.isDeleted=:value");
		strb.append(" and sch.from<=:fee and sch.to>=:fee " +
				" and :agreementDate>=sch.commissionSchema.effectiveDate " +
				" and :agreementDate<=sch.commissionSchema.expiryDate  ");
		
		Calendar c = Calendar.getInstance();
		c.set(Calendar.MONTH, affectMonth-1);
		c.set(Calendar.YEAR, affectYear);
		 
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("id", id);
		query.setParameter("fee", fee); 
		query.setParameter("agreementDate", DateUtil.formatDateByTime(c.getTime(), 2, 0, 0, 0));
		query.setParameter("value", CommonConstant.NOT_DELETED);
		 
		return (CommissionSchemaPaymentRange)query.uniqueResult();
	}

	public void deleteCommissionSchemaPaymentRangeBySchId(String id)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" delete from CommissionSchemaPaymentRange sch where sch.commissionSchema.commSchId=:id ");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("id", id);

		query.executeUpdate();
	}

	public void deleteCommissionSchemaMembershipBySchId(String id)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" delete from CommissionSchemaMembership sch where sch.commSch.commSchId=:id ");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("id", id);

		query.executeUpdate();
	}

	public List<Membership> getMembershipList() throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select mem from Membership mem where  mem.isDeleted=:value");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<Membership>)query.list();
	}

	public List<Agent> getSubAgentList(String agentId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select agent from Agent agent where  agent.isDeleted=:value and agent.parent.agentId=:agentId");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);

		return (List<Agent>)query.list();
	}

	public List<Agent> getSubAgentCodeNameList(String agentId,String code)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select agent from Agent agent where  agent.isDeleted=:value and agent.parent.agentId=:agentId");
		strb.append(" and agent.code=:code ");
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		query.setParameter("code", code);
		return (List<Agent>)query.list();
	}

	public List<Agent> getAgentApprovedList()throws Exception{
		Criteria criteria = getSession().createCriteria(Agent.class);
		criteria.add(Restrictions.eq("status", AgentConstant.AGENT_STATUS_APPROVE_AGENT));
		List<Agent> list=criteria.list();
		return list;
	}
	
	public List<Agent> getAgentForCommission()throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select agent from Agent agent where  agent.isDeleted=:value and agent.status=:status" +
				" and agent.agentType=:agentType and agent.schema.commissionType=:commissionType");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("status", AgentConstant.AGENT_STATUS_APPROVE_AGENT);
		query.setParameter("agentType", AgentConstant.AGENT_LEVEL_1);
		query.setParameter("commissionType", AgentConstant.AGENT_COMM_SCH_COMM_TYPE_RANGE);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Agent>)query.list();
		 
	}

	public List<Agent> getAgentListByLevel(String levelType)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select agent from Agent agent where  agent.isDeleted=:value and agent.agentType=:agentType");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentType", levelType);

		return (List<Agent>)query.list();
	}

	public List<AgreementControl> getAgreementControlByAgentId(String agentId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select control from AgreementControl control where control.isDeleted=:value ");
		strb.append(" and control.agent.agentId=:agentId ");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);

		return (List<AgreementControl>)query.list();
	}

	public AgentCommission getAgentCommissionByDate(Integer month,Integer year,String agentId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select comm from AgentCommission comm where comm.isDeleted=:value ");
		strb.append(" and comm.agent.agentId=:agentId and comm.month=:month and comm.year=:year ");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		query.setParameter("month", month);
		query.setParameter("year", year);

		return (AgentCommission)query.uniqueResult();
	}

	public CommissionSchemaMembership getCommissionSchemaMembership(String membershipId,String schId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select comm from CommissionSchemaMembership comm where comm.isDeleted=:value ");
		strb.append(" and comm.commSch.commSchId=:schId and comm.membership.membershipId=:membershipId ");

		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("membershipId", membershipId);
		query.setParameter("schId", schId);

		return (CommissionSchemaMembership)query.uniqueResult();
	}

	public List<AgentCommission> getAgentCommissionListByDateRange(String agentId,Date from,Date to)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select comm from AgentCommission comm where comm.isDeleted=:value ");
		strb.append("   and comm.agent.agentId=:agentId ");

		if(from!=null && to!=null){
			strb.append(" and comm.createdDate>=:from and comm.createdDate<=:to ");
		}


		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		if(from!=null && to!=null){
			query.setParameter("from", from);
			query.setParameter("to", to);
		}

		return (List<AgentCommission>)query.list();
	}
	
	public AgentCommission getAgentCommissionByAgreementIdAgentId(String agentId,String agreementId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select comm from AgentCommission comm where comm.isDeleted=:value ");
		strb.append("  and comm.agent.agentId=:agentId ");
		strb.append("  and comm.agreement.agreementId=:agreementId ");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		query.setParameter("agreementId", agreementId);
		
		return (AgentCommission)query.uniqueResult();
	}

	public List<AgentCommission> getAgentCommissionList(Agent agent)throws Exception{
		Criteria criteria = getSession().createCriteria(AgentCommission.class);
		criteria.add(Restrictions.eq("agent.agentId", agent.getAgentId()));
		criteria.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		
		List<AgentCommission> list=criteria.list();
		
		return list;
	}
}
